﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using CrystalDecisions.CrystalReports.Engine;

using LEEAAttendanceControl.Web.DataSets;

using System.Data;
using System.Data.SqlClient;
using CrystalDecisions.Shared;
using System.Threading;
using System.Globalization;

namespace LEEAAttendanceControl.Web.ReportPages
{
    public partial class Reports : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Thread.CurrentThread.CurrentCulture = (CultureInfo)Thread.CurrentThread.CurrentCulture.Clone();
            Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern = "dd/MM/yyyy";
    
            ReportDocument document = new ReportDocument();

            DataSet dataSet = null;

            string reportName = Request.QueryString["Name"].ToString();

            switch (reportName)
            {
                case "SummaryTimeCard":
                    dataSet = new SummarySet();

                    DataTable summaryTable = new DataTable("Summary Table");

                    Title = "Summary Time Card Report";

                    summaryTable = GetSummary();
                    dataSet.Tables[0].Merge(summaryTable);
                    MyReportViewer.ReuseParameterValuesOnRefresh = false;
                    MyReportViewer.EnableParameterPrompt = false;
                    MyReportViewer.ParameterFieldInfo = GetDateParamters();
                    document.Load(Server.MapPath("../Reports/SummaryTimeCard.rpt"));
                      
                  
                    break;

                case "DetailedTimeCard":
                    dataSet = new DetailedSet();

                    DataTable detailsTable = new DataTable("Details Table");

                    Title = "Detailed Time Card Report";

                    detailsTable = GetDetails();

                    dataSet.Tables[0].Merge(detailsTable);
                    MyReportViewer.ReuseParameterValuesOnRefresh = false;
                    MyReportViewer.EnableParameterPrompt = false;
                    MyReportViewer.ParameterFieldInfo = GetDateParamters();
                    
                    document.Load(Server.MapPath("../Reports/DetailedTimeCard.rpt"));

                    break;

                case "DetailedOvertime":
                    dataSet = new OvertimeSet();

                    DataTable overtimeTable = new DataTable("Overtime Table");

                    Title = "Detailed Overtime Report";

                    overtimeTable = GetOvertime();

                    dataSet.Tables[0].Merge(overtimeTable);

                    MyReportViewer.ReuseParameterValuesOnRefresh = false;
                    MyReportViewer.EnableParameterPrompt = false;
                    MyReportViewer.ParameterFieldInfo = GetDateParamters();
                    
                    document.Load(Server.MapPath("../Reports/DetailedOvertime.rpt"));

                    break;

                case "ShiftAssignments":
                    dataSet = new ShiftSet();

                    DataTable shiftsTable = new DataTable("Shifts Table");

                    Title = "Shift Assignments Report";

                    shiftsTable = GetShiftAssignments();

                    dataSet.Tables[0].Merge(shiftsTable);

                    document.Load(Server.MapPath("../Reports/ShiftAssignments.rpt"));

                    break;

                case "ExceptionAssignments":
                    dataSet = new ExceptionSet();

                    DataTable exceptionsTable = new DataTable("Exceptions Table");

                    Title = "Exception Assignments Report";

                    exceptionsTable = GetExceptionAssignments();

                    dataSet.Tables[0].Merge(exceptionsTable);

                    document.Load(Server.MapPath("../Reports/ExceptionAssignments.rpt"));

                    break;
                
                case "OnSitePresence":
                    dataSet = new PresenceSet();

                    DataTable presenceTable = new DataTable("Presence Table");

                    Title = "OnSite Presence Report";

                    presenceTable = GetPresence();

                    dataSet.Tables[0].Merge(presenceTable);

                    document.Load(Server.MapPath("../Reports/PresenceReport.rpt"));

                    break;

                case "LateComers":
                    dataSet = new LateSet();

                    DataTable lateTable = new DataTable("Late Table");

                    Title = "Late Comers Report";

                    lateTable = GetLateComers();

                    dataSet.Tables[0].Merge(lateTable);

                    document.Load(Server.MapPath("../Reports/LateComers.rpt"));

                    break;

                case "Absence":
                    dataSet = new AbsenceSet();

                    DataTable absenceTable = new DataTable("Absence Table");

                    Title = "Absence Report";

                    absenceTable = GetAbsence();

                    dataSet.Tables[0].Merge(absenceTable);

                    document.Load(Server.MapPath("../Reports/AbsenceReport.rpt"));

                    break;

                case "SavedHours":
                    dataSet = new SavedSet();

                    DataTable savedTable = new DataTable("Saved Table");

                    Title = "Saved Hours Report";

                    savedTable = GetSavedHours();

                    dataSet.Tables[0].Merge(savedTable);

                    document.Load(Server.MapPath("../Reports/SavedHours.rpt"));

                    break;

                case "MissingPunches":
                    dataSet = new MissingSet();

                    DataTable missingTable = new DataTable("Missing Table");

                    Title = "Missing Punches Report";
                    
                    missingTable = GetMissing();

                    dataSet.Tables[0].Merge(missingTable);

                    document.Load(Server.MapPath("../Reports/MissingPunches.rpt"));

                    break;
            }

            document.SetDataSource(dataSet);

            //MyReportViewer.SeparatePages = false;

            MyReportViewer.ReportSource = document;
        }

        ParameterFields GetDateParamters()
        {
            ParameterFields pfields = new ParameterFields();
            ParameterField datefromField = new ParameterField();
            ParameterDiscreteValue datefromdisvalue = new ParameterDiscreteValue();

            ParameterField datetoField = new ParameterField();
            ParameterDiscreteValue datetodisvalue = new ParameterDiscreteValue();

            datefromdisvalue.Value = Session["From"] != null ?string.Format(((DateTime)Session["From"]).ToShortDateString(),"dd/MM/yyyy") : "--";
            datefromField.Name = "DateFrom";
            datefromField.CurrentValues.Add(datefromdisvalue);
            pfields.Add(datefromField);

            datetoField.Name = "DateTo";
            datetodisvalue.Value = Session["To"] != null ?string.Format(((DateTime)Session["To"]).ToShortDateString(),"dd/MM/yyyy") : "--";
            datetoField.CurrentValues.Add(datetodisvalue);
            pfields.Add(datetoField);
            return pfields;
        }

        //--> Fill DataTable for 'Summary Time Card Report':
        private DataTable GetSummary()
        {
            DataTable summaryTable = new DataTable();
            summaryTable.Columns.Add("EmployeeCode", typeof(string));
            summaryTable.Columns.Add("EmployeeName", typeof(string));
            summaryTable.Columns.Add("Total", typeof(string));
            summaryTable.Columns.Add("Breaks", typeof(string));
            summaryTable.Columns.Add("Exceptions", typeof(string));
            summaryTable.Columns.Add("Paid", typeof(string));
            summaryTable.Columns.Add("Overtime", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime)Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (Employee employee in context.Employees.Where(emp => employees.Contains(emp.EmployeeID)).OrderBy(emp=>emp.FirstName))
            {
                DataRow row = summaryTable.NewRow();

                row["EmployeeCode"] =employee.EmployeeCode!=null?employee.EmployeeCode.ToString():"--";
                row["EmployeeName"] = employee.FirstName + " " + employee.LastName;

                List<Hour> hours = new List<Hour>();

                foreach (Hour hour in context.Hours)
                {
                    if (hour.ActualIN == null)
                    {
                        if (hour.EmployeeID == employee.EmployeeID && hour.ActualOUT.Value.Date >= from
                            && hour.ActualOUT.Value.Date <= to)
                        {
                            hours.Add(hour);
                        }
                    }
                    else
                    {
                        if (hour.EmployeeID == employee.EmployeeID && hour.ActualIN.Value.Date >= from
                            && hour.ActualIN.Value.Date <= to)
                        {
                            hours.Add(hour);
                        }
                    }
                }

                int total = hours.Sum(hh => hh.RoundedHours) ?? 0;

                row["Total"] = ConvertMinutesToHours(total);

                int breaks = hours.Sum(hh => hh.BreakTime) ?? 0;

                row["Breaks"] = ConvertMinutesToHours(breaks);

                int exceptions = 0;

                if (context.ExceptionAssignments.Where(ea => ea.EmployeeID == employee.EmployeeID).Count() > 0)
                {
                    foreach (ExceptionAssignment assignment in
                        context.ExceptionAssignments.Where(ea => ea.EmployeeID == employee.EmployeeID))
                    {
                        for (int i = 0; i <= assignment.DateTo.Value.Subtract(assignment.DateFrom.Value).Days; i++)
                        {
                            DateTime day = assignment.DateFrom.Value.AddDays(i).Date;

                            if (day >= from && day <= to)
                            {
                                exceptions += (int)context.Exeptions
                                    .Where(ex => ex.ID == assignment.ExceptionID).FirstOrDefault().DPH.Value * 60;
                            }
                        }
                    }
                }

                row["Exceptions"] = ConvertMinutesToHours(exceptions);

                row["Paid"] = ConvertMinutesToHours((total - breaks) + exceptions);

                row["Overtime"] = ConvertMinutesToHours(hours.Where(h => h.ActualOvertime > 0)
                    .Sum(hh => hh.RoundedOvertime) ?? 0);

                summaryTable.Rows.Add(row);
            }

            return summaryTable;
        }

        //--> Fill DataTable for 'Detailed Time Card Report':
        private DataTable GetDetails()
        {
            DataTable detailsTable = new DataTable();

            detailsTable.Columns.Add("EmployeeName", typeof(string));
            detailsTable.Columns.Add("Day", typeof(string));
            detailsTable.Columns.Add("Shift", typeof(string));
            detailsTable.Columns.Add("INTime", typeof(string));
            detailsTable.Columns.Add("OUTTime", typeof(string));
            detailsTable.Columns.Add("WorkHours", typeof(string));
            detailsTable.Columns.Add("Break", typeof(string));
            detailsTable.Columns.Add("Overtime", typeof(string));
            detailsTable.Columns.Add("EmployeeCode", typeof(string));
       
            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime)Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (Hour hour in context.Hours.Where(h => employees.Contains(h.EmployeeID.Value)).OrderBy(hh
                => hh.ActualIN))
            {
                if ((hour.ActualIN != null && hour.ActualIN.Value.Date >= from && hour.ActualIN.Value.Date <= to)
                    || (hour.ActualIN == null && hour.ActualOUT.Value.Date >= from && hour.ActualOUT.Value.Date <= to))
                {
                    DataRow row = detailsTable.NewRow();

                    Employee employee = context.Employees.Where(emp => emp.EmployeeID == hour.EmployeeID).FirstOrDefault();
               
                    row["EmployeeCode"] = employee.EmployeeCode != null ? employee.EmployeeCode.ToString() : "--";
               
                    row["EmployeeName"] = employee.FirstName + " " + employee.LastName;

                    row["Day"] = hour.ActualIN == null ? "" : hour.ActualIN.Value.ToString("dd/MM/yyyy");

                    row["Shift"] = hour.ShiftName;

                    row["INTime"] = hour.ActualIN == null ? "" : hour.ActualIN.Value.ToString("dd/MM/yyyy HH:mm tt");

                    row["OUTTime"] = hour.ActualOUT == null ? "" : hour.ActualOUT.Value.ToString("dd/MM/yyyy HH:mm tt");

                    row["WorkHours"] = hour.ActualHours == null ? "" : ConvertMinutesToHours(hour.ActualHours.Value);

                    row["Overtime"] = hour.ActualOvertime == null ? "00:00" : ConvertMinutesToHours(hour.ActualOvertime.Value);

                    row["Break"] = hour.BreakTime == null ? "" : hour.BreakTime.Value.ToString();

                    detailsTable.Rows.Add(row);
                }
            }

            return detailsTable;
        }

        //--> Fill DataTable for 'Detailed Overtime Report':
        public DataTable GetOvertime()
        {
            DataTable overtimeTable = new DataTable();

            overtimeTable.Columns.Add("EmployeeName", typeof(string));
            overtimeTable.Columns.Add("Day", typeof(string));
            overtimeTable.Columns.Add("Shift", typeof(string));
            overtimeTable.Columns.Add("INTime", typeof(string));
            overtimeTable.Columns.Add("ActualOvertime", typeof(string));
            overtimeTable.Columns.Add("RoundedOvertime", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime)Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (Hour hour in context.Hours.Where(h => employees.Contains(h.EmployeeID.Value)
                && h.ActualOvertime.Value > 0))
            {
                if (hour.ActualIN.Value.Date >= from && hour.ActualIN.Value.Date <= to)
                {
                    DataRow row = overtimeTable.NewRow();

                    Employee employee = context.Employees.Where(emp
                        => emp.EmployeeID == hour.EmployeeID.Value).FirstOrDefault();

                    row["EmployeeName"] = employee.FirstName + " " + employee.LastName;

                    row["Day"] = hour.ActualIN.Value.ToString("dd/MM/yyyy");

                    row["Shift"] = hour.ShiftName;

                    row["INTime"] = hour.ActualIN.Value.ToString("HH:mm tt");

                    row["ActualOvertime"] = ConvertMinutesToHours(hour.ActualOvertime.Value);

                    row["RoundedOvertime"] = ConvertMinutesToHours(hour.RoundedOvertime.Value);

                    overtimeTable.Rows.Add(row);
                }
            }

            return overtimeTable;
        }

        //--> Fill DataTable for 'Shift Assignments Report':
        public DataTable GetShiftAssignments()
        {
            DataTable shiftsTable = new DataTable();

            shiftsTable.Columns.Add("EmployeeName", typeof(string));
            shiftsTable.Columns.Add("Shift", typeof(string));
            shiftsTable.Columns.Add("Saturday", typeof(string));
            shiftsTable.Columns.Add("Sunday", typeof(string));
            shiftsTable.Columns.Add("Monday", typeof(string));
            shiftsTable.Columns.Add("Tuesday", typeof(string));
            shiftsTable.Columns.Add("Wednesday", typeof(string));
            shiftsTable.Columns.Add("Thursday", typeof(string));
            shiftsTable.Columns.Add("Friday", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime)Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (ShiftAssignment assignment in context.ShiftAssignments.Where(ass
                => employees.Contains(ass.EmployeeID.Value)))
            {
                DataRow row = shiftsTable.NewRow();

                row["EmployeeName"] = assignment.Employee.FirstName + " " + assignment.Employee.LastName;
                
                row["Shift"] = context.Shifts.Where(sh => sh.ID == assignment.ShiftID.Value).FirstOrDefault().ShiftName;

                for (int i = 1; i < 8; i++)
                {
                    if (context.ShiftAssignmentDays.Where(sa => sa.DayID == i
                                && sa.ShiftAssignmentID == assignment.ID).Count() > 0)
                    {
                        row[context.WeekDays.Where(wd => wd.DayID == i).FirstOrDefault().DayName] = "Yes";
                    }
                    else
                    {
                        row[context.WeekDays.Where(wd => wd.DayID == i).FirstOrDefault().DayName] = "";
                    }
                }

                shiftsTable.Rows.Add(row);
            }

            return shiftsTable;
        }

        //--> Fill DataTable 'Exception Assignments Report':
        public DataTable GetExceptionAssignments()
        {
            DataTable exceptionsTable = new DataTable();

            exceptionsTable.Columns.Add("EmployeeName", typeof(string));
            exceptionsTable.Columns.Add("ExceptionName", typeof(string));
            exceptionsTable.Columns.Add("HPD", typeof(string));
            exceptionsTable.Columns.Add("From", typeof(string));
            exceptionsTable.Columns.Add("To", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime)Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (ExceptionAssignment assignment in context.ExceptionAssignments.Where(ea
                => employees.Contains(ea.EmployeeID.Value)))
            {
                if (assignment.DateFrom.Value.Date >= from && assignment.DateTo.Value.Date <= to)
                {
                    DataRow row = exceptionsTable.NewRow();

                    Employee employee = context.Employees.Where(emp
                        => emp.EmployeeID == assignment.EmployeeID.Value).FirstOrDefault(); ;

                    row["EmployeeName"] = employee.FirstName + " " + employee.LastName;

                    row["ExceptionName"] = assignment.Exeption.Name;

                    row["HPD"] = ConvertMinutesToHours(((int)assignment.Exeption.DPH.Value) * 60);

                    row["From"] = assignment.DateFrom.Value.ToString("dd/MM/yyyy");

                    row["To"] = assignment.DateTo.Value.ToString("dd/MM/yyyy");

                    exceptionsTable.Rows.Add(row);
                }
            }

            return exceptionsTable;
        }

        //--> Fill DataTable for 'OnSite Presence Report':
        public DataTable GetPresence()
        {
            DataTable presenceTable = new DataTable();

            presenceTable.Columns.Add("EmployeeName", typeof(string));
            presenceTable.Columns.Add("Department", typeof(string));
            presenceTable.Columns.Add("Shift", typeof(string));
            presenceTable.Columns.Add("INTime", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];

            DateTime day = DateTime.Now.Date;

            foreach (int empID in employees)
            {
                DataRow row = presenceTable.NewRow();

                Employee employee = context.Employees.Where(emp => emp.EmployeeID == empID).FirstOrDefault();

                row["EmployeeName"] = employee.FirstName + " " + employee.LastName;

                row["Department"] = employee.Department.DepartmentName;

                Punch punch = context.Punches.Where(p => p.PunchType == "IN" && p.EmployeeID == empID
                    && p.DataTime.Value.Day == day.Day && p.DataTime.Value.Month == day.Month
                    && p.DataTime.Value.Year == day.Year).FirstOrDefault();

                if (punch != null)
                {
                    row["Shift"] = context.Shifts.Where(sh => sh.ID == punch.ShiftID.Value).FirstOrDefault().ShiftName;

                    row["INTime"] = punch.DataTime.Value.ToString("HH:mm tt");
                }
                else
                {
                    row["Shift"] = "None";

                    row["INTime"] = "No IN";
                }

                presenceTable.Rows.Add(row);
            }

            return presenceTable;
        }

        //--> Fill DataTable for 'Late Comers Report':
        public DataTable GetLateComers()
        {
            DataTable comersTable = new DataTable();

            comersTable.Columns.Add("EmployeeName", typeof(string));
            comersTable.Columns.Add("Day", typeof(string));
            comersTable.Columns.Add("Shift", typeof(string));
            comersTable.Columns.Add("ShiftStart", typeof(string));
            comersTable.Columns.Add("INTime", typeof(string));
            comersTable.Columns.Add("LatePeriod", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime) Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (Hour hour in context.Hours.Where(h => employees.Contains(h.EmployeeID.Value)
                && h.ActualIN != null))
            {
                if (hour.ActualIN.Value.Date >= from && hour.ActualIN.Value.Date <= to)
                {
                    Shift shift = context.Shifts.Where(sh => sh.ID == hour.ShiftID.Value).FirstOrDefault();

                    if (hour.ActualIN.Value.Hour > shift.TimeFrom.Value.Hour
                        || (hour.ActualIN.Value.Hour == shift.TimeFrom.Value.Hour
                            && hour.ActualIN.Value.Minute > shift.TimeFrom.Value.Minute))
                    {
                        DataRow row = comersTable.NewRow();

                        Employee employee = context.Employees.Where(emp
                            => emp.EmployeeID == hour.EmployeeID.Value).FirstOrDefault();

                        row["EmployeeName"] = employee.FirstName + " " + employee.LastName;

                        row["Day"] = hour.ActualIN.Value.ToString("dd/MM/yyyy");

                        row["Shift"] = shift.ShiftName;

                        row["ShiftStart"] = shift.TimeFrom.Value.ToString("HH:mm tt");

                        row["INTime"] = hour.ActualIN.Value.ToString("HH:mm tt");

                        row["LatePeriod"] = ConvertMinutesToHours(
                            (hour.ActualIN.Value.Subtract(shift.TimeFrom.Value).Hours * 60)
                                + hour.ActualIN.Value.Subtract(shift.TimeFrom.Value).Minutes);

                        comersTable.Rows.Add(row);
                    }
                }
            }

            return comersTable;
        }

        //--> Fill DataTable for 'Absence Report':
        public DataTable GetAbsence()
        {
            DataTable absenceTable = new DataTable();

            absenceTable.Columns.Add("EmployeeName", typeof(string));
            absenceTable.Columns.Add("Department", typeof(string));
            absenceTable.Columns.Add("DayName", typeof(string));
            absenceTable.Columns.Add("DayDate", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime)Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (int empID in employees)
            {
                Employee employee = context.Employees.Where(emp => emp.EmployeeID == empID).FirstOrDefault();

                for (DateTime day = from.Date; day <= to.Date; day = day.AddDays(1))
                {
                    string name = day.ToString("dddd");

                    int dayID = context.WeekDays.Where(wd => wd.DayName == name).FirstOrDefault().DayID;

                    if (context.Hours.Where(h => h.ActualIN != null && h.EmployeeID.Value == empID
                        && h.ActualIN.Value.Year == day.Year && h.ActualIN.Value.Month == day.Month
                        && h.ActualIN.Value.Day == day.Day).Count() == 0)
                    {
                        foreach (ShiftAssignment assignment in context.ShiftAssignments.Where(sa => sa.EmployeeID == empID))
                        {
                            if (day.Date >= assignment.AssignmentFrom.Value.Date
                                && day.Date <= assignment.AssignmentTo.Value.Date
                                && context.ShiftAssignmentDays.Where(sad
                                    => sad.DayID == dayID && sad.ShiftAssignmentID == assignment.ID).Count() > 0)
                            {
                                DataRow row = absenceTable.NewRow();

                                row["EmployeeName"] = employee.FirstName + " " + employee.LastName;
                                row["Department"] = employee.Department.DepartmentName;

                                row["DayName"] = name;
                                row["DayDate"] = day.ToString("dd/MM/yyyy");

                                absenceTable.Rows.Add(row);
                            }
                        }
                    }
                }
            }

            return absenceTable;
        }

        //--> Fill DataTable for 'Saved Hours Report':
        public DataTable GetSavedHours()
        {
            DataTable savedTable = new DataTable();

            savedTable.Columns.Add("EmployeeName", typeof(string));
            savedTable.Columns.Add("Department", typeof(string));
            savedTable.Columns.Add("ActualTime", typeof(string));
            savedTable.Columns.Add("RoundedTime", typeof(string));
            savedTable.Columns.Add("SavedHours", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime)Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (int empID in employees)
            {
                DataRow row = savedTable.NewRow();

                Employee employee = context.Employees.Where(emp => emp.EmployeeID == empID).FirstOrDefault();

                List<Hour> hours = new List<Hour>();

                foreach (Hour hour in context.Hours.Where(h => h.EmployeeID.Value == empID))
                {
                    if (hour.ActualIN != null && hour.ActualOUT != null && hour.ActualIN.Value.Date >= from
                        && hour.ActualIN.Value.Date <= to)
                    {
                        hours.Add(hour);
                    }
                }

                row["EmployeeName"] = employee.FirstName + " " + employee.LastName;

                row["Department"] = employee.Department.DepartmentName;

                int actual = hours.Sum(h => h.ActualHours == null ? 0 : h.ActualHours.Value);

                row["ActualTime"] = ConvertMinutesToHours(actual);

                int rounded = hours.Sum(h => h.RoundedHours == null ? 0 : h.RoundedHours.Value);

                row["RoundedTime"] = ConvertMinutesToHours(rounded);

                string saved = rounded - actual < 0 ? "- " : "  ";

                saved += ConvertMinutesToHours(rounded > actual ? rounded - actual : actual - rounded).ToString();

                row["SavedHours"] = saved;

                savedTable.Rows.Add(row);
            }

            return savedTable;
        }

        //--> Fill DataTable for 'Missing Punches Report':
        public DataTable GetMissing()
        {
            DataTable missingTable = new DataTable();

            missingTable.Columns.Add("EmployeeName", typeof(string));
            missingTable.Columns.Add("Shift", typeof(string));
            missingTable.Columns.Add("MissedPunch", typeof(string));
            missingTable.Columns.Add("INTime", typeof(string));
            missingTable.Columns.Add("OUTTime", typeof(string));

            AttendanceDBEntities context = new AttendanceDBEntities();

            List<int> employees = (List<int>)Session["Employees"];
            DateTime from = (DateTime)Session["From"];
            DateTime to = (DateTime)Session["To"];

            foreach (Hour hour in context.Hours.Where(h => employees.Contains(h.EmployeeID.Value)))
            {
                if (((hour.ActualIN == null && hour.ActualOUT.Value.Date >= from && hour.ActualOUT.Value.Date <= to)
                    || (hour.ActualOUT == null && hour.ActualIN.Value.Date >= from && hour.ActualIN.Value.Date <= to)))
                {
                    DataRow row = missingTable.NewRow();

                    Employee employee = context.Employees.Where(emp
                        => emp.EmployeeID == hour.EmployeeID.Value).FirstOrDefault();

                    row["EmployeeName"] = employee.FirstName + " " + employee.LastName;

                    row["Shift"] = hour.ShiftName;

                    if (hour.ActualIN == null && hour.ActualOUT != null)
                    {
                        row["MissedPunch"] = "IN";
                        row["INTime"] = "";
                        row["OUTTime"] = hour.ActualOUT.Value.ToString("dd/MM/yyyy HH:mm tt");
                    }

                    if (hour.ActualIN != null && hour.ActualOUT == null)
                    {
                        row["MissedPunch"] = "OUT";
                        row["INTime"] = hour.ActualIN.Value.ToString("dd/MM/yyyy HH:mm tt");
                        row["OUTTime"] = "";
                    }

                    missingTable.Rows.Add(row);
                }
            }

            return missingTable;
        }

        private string ConvertMinutesToHours(int minutes)
        {
            return (((int)(minutes / 60)) >= 10 ? ((int)(minutes / 60)).ToString() : '0' + ((int)(minutes / 60)).ToString())
                + ":" + ((minutes % 60) >= 10 ? (minutes % 60).ToString() : '0' + (minutes % 60).ToString());
        }
    }
}